package be.tf.freemaind.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import be.tf.freemaind.exceptions.FreeMaindException;
import be.tf.freemaind.model.Category;
import be.tf.freemaind.model.Post;

import com.mysql.jdbc.Statement;

public class PostDAO extends Dao<Post>{
	static final String sqlNode="select c.idCategory,c.name as cname," +
			"c.description as cdescription,f.idclef,f.name as fname," +
			"f.description as fdescription,idpost,title,file,p.description as pdescription " +
			",sdate,pdate from Post p "+ 
			"inner join Category c on c.IdCategory=p.Category_IdCategory "+
			"inner join clef f on f.idclef=p.clef_idclef "+
			"order by IdCategory,Idclef;";
	public PostDAO(Statement stm) {
		super(stm);
		// TODO Auto-generated constructor stub
	}

	@Override
	public List<Post> findAll() throws SQLException {
		ArrayList<Post> posts=new ArrayList<Post>();
		Post post;
	
			ResultSet rs=stm.executeQuery(sqlNode);
			while(rs.next()){
				post = null;
				builObject(rs, post);
				posts.add(post);
			}

		return posts;
	}

	@Override
	public Post find(int id) throws SQLException {
		// TODO Auto-generated method stub
		
		Post post;
		
			ResultSet rs=stm.executeQuery("SELECT * FROM Post where idpost="+id);
			rs.next();

			post=new Post();
			post.setCategory_idCategory(rs.getLong("category_idCategory"));
			post.setClef_idclef(rs.getLong("clef_idclef"));
			post.setDescription(rs.getString("description"));
			post.setFile(rs.getString("file"));
			post.setIdpost(rs.getLong("idpost"));
			post.setPdate(rs.getDate("pdate"));
			post.setSdate(rs.getDate("sdate"));
			post.setTitle(rs.getString("title"));
			post.setUser_iduser(rs.getLong("user_iduser"));
			
		return post;
	}
	
	private void builObject(ResultSet rs,Post post) throws SQLException {
		post=new Post();
		post.setCategory_idCategory(rs.getLong("category_idCategory"));
		post.setClef_idclef(rs.getLong("clef_idclef"));
		post.setDescription(rs.getString("description"));
		post.setFile(rs.getString("file"));
		post.setIdpost(rs.getLong("idpost"));
		post.setPdate(rs.getDate("pdate"));
		post.setSdate(rs.getDate("sdate"));
		post.setTitle(rs.getString("title"));
		post.setUser_iduser(rs.getLong("user_iduser"));
		
		post.setCategory(new CategoryDAO(stm).find((int)rs.getLong("category_idCategory")));
		post.setClef(new ClefDAO(stm).find((int)rs.getLong("clef_idclef")));
		
	}
	}
	
	

